#!/usr/bin/php
<?php

const CHINESE_NUM = array('一', '二', '三', '四', '五', '六', '七', '八', '九', '十');
const CHINESE_SIZE = array('小', '中', '大');

const FAMILY_NAME = array('朱', '张', '游', '邹',
    '谢', '石', '乐', '李', '王', '赵', '孙',
    '吴', '杨', '欧阳', '公孙', '程', '曾');

const COURSE_NAME = array('语文', '数学', '英语', '化学',
    '物理', '音乐', '体育', '政治', '几何', '地理',
    '生物');

function create_table($db, $table_name, $fields = array())
{
    $query = $db->query("select name from sqlite_master where type='table' and name='$table_name'");
    $data = $query->fetchArray();
    if ($data === FALSE) {
        $sql = "CREATE TABLE $table_name (";
        foreach ($fields as $field) {
            $sql .= $field.",";
        }
        $sql = rtrim($sql, ',');
        $sql .= ');';

        return $db->exec($sql);
    }

    return FALSE;
}

function insert($db, $table, $fields = array())
{
    $sql = 'INSERT INTO '.$table;

    $field_name = ' (';
    $field_value = ' (';

    foreach ($fields as $key => $val) {
        $field_name .= $key.',';
        $field_value .= "'".$val."',";
    }
    $field_name = rtrim($field_name, ",");
    $field_value = rtrim($field_value, ",");

    $sql .= $field_name.')';
    $sql .= ' VALUES '.$field_value.')';

    // echo $sql, PHP_EOL;

    if ($db->exec($sql))
        return $db->lastInsertRowID();
    return FALSE;
}

function create_sys_table($db)
{
    create_table($db, "student", array(
        'id INTEGER PRIMARY KEY',
        'name TEXT NOT NULL COLLATE NOCASE',
        'sex INT DEFAULT 1',
        'age INT NOT NULL CHECK(age >= 7)',
        'phone TEXT NOT NULL UNIQUE',
        'cid INT NOT NULL'
    ));

    create_table($db, "class", array(
        'id INTEGER PRIMARY KEY',
        'name TEXT NOT NULL COLLATE NOCASE',
        'num INT DEFAULT 0',
        'sid INT NOT NULL'
    ));

    create_table($db, "school", array(
        'id INTEGER PRIMARY KEY',
        'name TEXT NOT NULL COLLATE NOCASE'
    ));

    create_table($db, "course", array(
        'id INTEGER PRIMARY KEY',
        'name TEXT NOT NULL COLLATE NOCASE',
        'sid INT NOT NULL'
    ));

    create_table($db, "grade", array(
        'id INTEGER PRIMARY KEY',
        'stu_id INT NOT NULL',
        'course_id INT NOT NULL',
        'score INT NOT NULL',
        'time TEXT NOT NULL'
    ));
}

function drop_table($db)
{
    $db->exec("DROP TABLE school;");
    $db->exec("DROP TABLE class;");
    $db->exec("DROP TABLE student;");
    $db->exec("DROP TABLE course;");
    $db->exec("DROP TABLE grade;");
}

function rand_school($db)
{
    $name = '第';
    $name .= CHINESE_NUM[mt_rand(0, 9)];
    $name .= CHINESE_SIZE[mt_rand(0, 2)];
    $name .= '学';
    return insert($db, 'school', array('name' => $name));
}

function rand_class($db, $sid, $stu_total)
{
    $name = CHINESE_NUM[mt_rand(0, 9)].'年级一班';
    return insert($db, 'class', array(
        'name' => $name,
        'num' => $stu_total,
        'sid' => $sid,
    ));
}

function rand_name($num = 2)
{
    $name = FAMILY_NAME[mt_rand(0, 16)];
    for ($i = 0; $i < $num; ++$i) {
        $type = mt_rand(0, 2);
        switch ($type) {
            case 0:
                $name .= FAMILY_NAME[mt_rand(0, 16)];
                break;
            case 1:
                $name .= CHINESE_SIZE[mt_rand(0, 2)];
                break;
            case 2:
                $name .= CHINESE_NUM[mt_rand(0, 9)];
                break;
        }
    }

    return $name;
}

function rand_stu($db, $cid)
{
    $name = CHINESE_NUM[mt_rand(0, 9)].'年级一班';
    while (1) {
        $phone = '13'.mt_rand(0, 9).mt_rand(10000000, 99999999);

        /*
         * $ret = $db->querySingle("select id from student where phone='$phone'");
         * var_dump($ret);
         * exit(0);
         */

        if (!$db->querySingle("select id from student where phone='$phone'"))
            break;
    }
    return insert($db, 'student', array(
        'name' => rand_name(mt_rand(2, 4)),
        'sex' => mt_rand(0, 1),
        'age' => mt_rand(7, 18),
        'phone' => $phone,
        'cid' => $cid,
    ));
}

function rand_class_stu($db, $sid, $mt)
{
    $class_total = mt_rand($mt['class'][0], $mt['class'][1]);
    for ($i = 0; $i < $class_total; ++$i) {
        $stu_total = mt_rand($mt['stu'][0], $mt['stu'][1]);
        $cid = rand_class($db, $sid, $stu_total);
        for ($j = 0; $j < $stu_total; ++$j) {
            rand_stu($db, $cid);
        }
    }
}

function rand_cousre($db, $sid, $mt)
{
    $cousre_total = mt_rand($mt[0], $mt[1]);

    for ($i = 0; $i < $cousre_total; ++$i) {
        insert($db, 'course', array(
            'name' => COURSE_NAME[$i],
            'sid' =>  $sid
        ));
    }
}


function rand_grade($db, $mt)
{
    $total = mt_rand($mt[0], $mt[1]);

    $obj = $db->query("select id from student;");
    $stu = array();
    while ($res = $obj->fetchArray(SQLITE3_ASSOC))
        $stu[] = $res['id'];

    $obj = $db->query("select id from course");
    $course = array();
    while ($res = $obj->fetchArray(SQLITE3_ASSOC))
        $course[] = $res['id'];

    $time_start = mktime(0, 0, 0, 1, 1, 2015);
    $time_end = mktime(0, 0, 0, 1, 1, 2017);

    for ($i = 0; $i < $total; ++$i) {
        insert($db, 'grade', array(
            'stu_id' => $stu[array_rand($stu)],
            'course_id' => $course[array_rand($course)],
            'score' => mt_rand(0, 101),
            'time' => mt_rand($time_start, $time_end)
        ));
    }
}

function rand_data($db, $mt)
{
    $school_total = mt_rand($mt['school'][0], $mt['school'][1]);

    for ($i = 0; $i < $school_total; ++$i) {
        $sid = rand_school($db);
        rand_class_stu($db, $sid, $mt);
        rand_cousre($db, $sid, $mt['course']);
    }
    rand_grade($db, $mt['grade']);
}

function main($argc, & $argv)
{
    $db = new SQLite3('kyo.db');
    if (!$db)
        exit('SQLite3 kyo.db failed!\n');

    drop_table($db);
    create_sys_table($db);
    rand_data($db, array(
        'school' => array(3, 5),
        'class' => array(3, 10),
        'stu' => array(5, 20),
        'course' => array(5, 10),
        'grade' => array(50, 100)
    ));

    return 0;
}

exit(main($argc, $argv));

